﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using InsideDL.MISService;
using InsideDL.MISControl;
using InsideDL.MISData;
using System.Diagnostics;


namespace InsideDL
{
    public partial class frmCIBSubsidyImport : MISControl.frmMatrix
    {
        protected EXLAPP exlAPP = null;
        protected string bat = "TransCIB001.bat";


        public frmCIBSubsidyImport()
        {
            InitializeComponent();
            ucIMFile.SelectClick += new EventHandler(getExcelSheetList);
            this.controlList = new List<ucSelFileOrPath>
            {
                ucIMFile
            };
            ucExecute1.ExecuteClick += new EventHandler(ucClickFacade);
            ucExecute_Exit.ExecuteClick += new EventHandler(ucClickFacade);
            ucYM.Text = Utility.GetCurrentYM(DateTime.Now.AddMonths(-1));
            this.FormClosed += new FormClosedEventHandler(frmCIB001_FormClosed);
            chkDel.Checked = true;  
        }

        protected override void execute()
        {
            if (base.Vaildation().Equals(false))
            { return; }

            DataCIBSubsidyImport subsidy = new DataCIBSubsidyImport();

            StringBuilder sb = new StringBuilder();
            string mon = Convert.ToInt16(ucYM.Text.Substring(4,2)).ToString() ;

            for (int i = 0; i < chkLB.Items.Count; i++)
            {
                if (chkLB.GetItemChecked(i).Equals(true))
                {
                    string sql = chkGOV.Checked
                        ? subsidy.GetGOVSQL(ucYM.Text
                            , chkLB.Items[i].ToString(),mon)
                        : subsidy.GetSQL(ucYM.Text, chkLB.Items[i].ToString());


                    sb.Append(
                        subsidy.buildInsertStatement(
                            chkLB.Items[i].ToString()
                            , ucIMFile.Txt
                            , ucYM.Text
                            ,chkDel.Checked
                            , string.Format("{0}.{1}",
                                    System.IO.Path.GetFileName(ucIMFile.Txt)
                                    , chkLB.Items[i].ToString())                                    
                                    ,sql
                                    )
                            );
                }
            }

            //sb.Append(
            //            subsidy.buildInsertStatement(
            //                System.IO.Path.GetFileName(ucIMFile.Txt)
            //                ,System.IO.Path.GetDirectoryName(ucIMFile.Txt)
            //                , ucYM.Text)
            //                );

            //把產生出來的資料存成 .sql
            string tmp = string.Format("{0}\\Subsidy.sql", Program.TmpPath());
            Program.CreateFile(tmp, sb.ToString());
            createSQLBat();

            tmp = string.Format("{0}\\{1}", Program.TmpPath(), bat);

            System.Diagnostics.ProcessStartInfo p = new System.Diagnostics.ProcessStartInfo(tmp);
            p.WindowStyle = ProcessWindowStyle.Normal;
            Process process = Process.Start(p);
            process.WaitForExit();
            if (!process.HasExited)
            {
                process.Kill();
            }


            showMessage(this.Text, "完成！");
        }

        /// <summary>
        /// 生出可以執行的BAT檔
        /// </summary>
        void createSQLBat()
        {
            string str, tmp;
            str = string.Format(@"
OSQL -S (local)\SQLEXPRESS -E -i {0}\Subsidy.sql
PAUSE
", Program.TmpPath());
            tmp = string.Format("{0}\\{1}", Program.TmpPath(), bat);
            Program.CreateFile(tmp, str);
        }


        protected void getExcelSheetList(object sender, EventArgs e)
        {
            if (exlAPP == null)
            {
                exlAPP = new EXLAPP();
            }

            while (chkLB.Items.Count > 0)
            {
                chkLB.Items.Remove(chkLB.Items[0]);                
            }

            exlAPP.OpenFile(ucIMFile.Txt);
            List<string> shtList = exlAPP.GetSheetList();
            foreach (string str in shtList)
            {
                chkLB.Items.Add(str);
            }
            exlAPP.CloseFile();
        }

        void frmCIB001_FormClosed(object sender, FormClosedEventArgs e)
        {
            if (exlAPP != null)
            {
                exlAPP.SaveCloseXLS();
                exlAPP = null;
            }
        }

     
    }
}
